﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using NPOI.SS.Formula.Functions;
using XiAnAirport.BUS;
using XiAnAirport.Models;

namespace XiAnAirport.DAL
{
    public class MaintainDAL
    {
        public string AddMaintain(MaintainInfo info)
        {
            string strSql = string.Format("INSERT INTO routine_maintain (elevatorID,maintenanceNo,maintenanceDatetime,maintenanceProperties,maintenancestateId,maintenanceResult,workattribute,preparationTime)VALUES({0},'{1}','{2}','{4}',{5},'{6}',3,'{7}')",
                info.elevatorID, info.maintenanceNo, info.maintenanceDatetime, info.maintenanceGroupID, info.maintenanceProperties, 0, "", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail" + e.ToString();
            }
        }

        public string InsertMaintain(MaintainInfo info)
        {
            string strSql = string.Format("INSERT INTO routine_maintain (elevatorID,maintenanceNo,maintenanceDatetime,maintenanceProperties,maintenancestateId,maintenanceResult,workattribute,preparationTime,receiveTime,arriveTime,completeTime,acceptanceTime,receiver,receiverPhone,receiverNo)VALUES({0},'{1}',{2},'{3}',{13},'{4}',3,{5},{6},{7},{8},{9},'{10}','{11}','{12}')",
                info.elevatorID, info.maintenanceNo, info.maintenanceDatetime, info.maintenanceProperties, info.maintenanceResult, info.preparationTime, info.receiveTime, info.arriveTime, info.completeTime, info.acceptanceTime, info.receiver, info.receiverPhone, info.receiverNo, info.maintenancestateId);
            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail" + e.ToString();
            }
        }

        public List<MaintainInfo> QueryMaintainHistoryList(MaintainInfo maininfo)
        {
            try
            {
                List<MaintainInfo> list = new List<MaintainInfo>();
                string strSql = "SELECT vi_maintaininfo.*,area.areaName AS mainAreaName FROM vi_maintaininfo LEFT JOIN area ON vi_maintaininfo.mainAreaId=area.id WHERE vi_maintaininfo.maintenancestateId=4 AND vi_maintaininfo.maintenanceProperties!='年度保养'";

                if (maininfo.elevatorID > 0)
                {
                    strSql += " AND vi_maintaininfo.elevatorID=@elevatorID";
                }
                if (!string.IsNullOrEmpty(maininfo.elevatorName))
                {
                    strSql += " AND vi_maintaininfo.elevatorName LIKE @elevatorName";
                }
                if (!string.IsNullOrEmpty(maininfo.startTime))
                {
                    strSql += " AND DATE(vi_maintaininfo.maintenanceDatetime) >=DATE(@startTime)";
                }
                if (!string.IsNullOrEmpty(maininfo.endTime))
                {
                    strSql += " AND DATE(vi_maintaininfo.maintenanceDatetime) <=DATE(@endTime)";
                }

                strSql += " ORDER BY vi_maintaininfo.maintenanceDatetime Desc";
                if (maininfo.Recordhead > -1 && maininfo.Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likename = "%" + maininfo.elevatorNo + "%";
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorID", maininfo.elevatorID);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(maininfo.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(maininfo.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", maininfo.Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail", maininfo.Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<MaintainInfo>(dataTable);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public List<MaintainInfo> QueryMaintainHistoryByIds(MaintainInfo maininfo)
        {
            string strSql = "SELECT vi_maintaininfo.*,area.areaName AS mainAreaName FROM vi_maintaininfo LEFT JOIN area ON vi_maintaininfo.mainAreaId=area.id WHERE vi_maintaininfo.id in(" + maininfo.ids + ") ";

            return SqlHelper<MaintainInfo>.SqlList(strSql);
        }

        public int QueryMaintainHistoryCount(MaintainInfo maininfo)
        {
            int count = 0;
            try
            {
                string strSql = "SELECT COUNT(1) FROM vi_maintaininfo WHERE maintenancestateId=4 AND maintenanceProperties!='年度保养'";

                if (maininfo.elevatorID > 0)
                {
                    strSql += " AND vi_maintaininfo.elevatorID=@elevatorID";
                }
                if (!string.IsNullOrEmpty(maininfo.elevatorName))
                {
                    strSql += " AND vi_maintaininfo.elevatorName LIKE @elevatorName";
                }
                if (!string.IsNullOrEmpty(maininfo.startTime))
                {
                    strSql += " AND DATE(vi_maintaininfo.maintenanceDatetime) >=DATE(@startTime)";
                }
                if (!string.IsNullOrEmpty(maininfo.endTime))
                {
                    strSql += " AND DATE(vi_maintaininfo.maintenanceDatetime) <=DATE(@endTime)";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likeno = "%" + maininfo.elevatorName + "%";
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{likeno}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorID", maininfo.elevatorID);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(maininfo.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(maininfo.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch
            {

            }

            return count;
        }

        public MaintainInfo LoadMaintain(int maintainid)
        {
            string strSql = "SELECT * FROM vi_maintaininfo WHERE id=" + maintainid;
            return SqlHelper<MaintainInfo>.SqlSingleData(strSql);
        }

        public string UpdateMaintainTime(MaintainInfo maininfo)
        {
            string strSql = "UPDATE routine_maintain SET";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(maininfo.receiveTime))
            {
                sqlwhere += " receiveTime = '" + maininfo.receiveTime + "',";
            }
            if (!string.IsNullOrEmpty(maininfo.arriveTime))
            {
                sqlwhere += " arriveTime = '" + maininfo.arriveTime + "',";
            }
            if (!string.IsNullOrEmpty(maininfo.completeTime))
            {
                sqlwhere += " completeTime = '" + maininfo.completeTime + "',";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += sqlwhere.Substring(0, sqlwhere.Length - 1) + " where id=" + maininfo.id;
            }

            try
            {
                if (SqlHelper<MaintainInfo>.SqlExecute(strSql))
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                LogHelper.Log(e.ToString());
                return "fail," + e.ToString();
            }
        }

        public string AcceptanceMaintain(MaintainInfo maininfo)
        {
            string strSql = "UPDATE routine_maintain SET maintenancestateId=4,workattribute=3,acceptanceTime='" + DateTime.Now.ToString() + "',maintenanceResult='正常' WHERE id=" + maininfo.id;

            int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

            if (count > 0)
            {
                return "success";
            }
            return "fail";
        }

        public List<MaintainInfo> QueryMaintainPlanInfo(MaintainInfo maininfo)
        {
            try
            {
                List<MaintainInfo> list = new List<MaintainInfo>();
                string strSql = "SELECT vi_maintaininfo.*,area.areaName AS mainAreaName FROM vi_maintaininfo LEFT JOIN area ON vi_maintaininfo.mainAreaId=area.id WHERE vi_maintaininfo.maintenancestateId!=4";

                if (maininfo.elevatorID > 0)
                {
                    strSql += " AND vi_maintaininfo.elevatorID=@elevatorID";
                }
                if (!string.IsNullOrEmpty(maininfo.elevatorName))
                {
                    strSql += " AND vi_maintaininfo.elevatorName LIKE @elevatorName";
                }
                if (!string.IsNullOrEmpty(maininfo.startTime))
                {
                    strSql += " AND DATE(vi_maintaininfo.maintenanceDatetime) >=DATE(@startTime)";
                }
                if (!string.IsNullOrEmpty(maininfo.endTime))
                {
                    strSql += " AND DATE(vi_maintaininfo.maintenanceDatetime) <=DATE(@endTime)";
                }

                strSql += " ORDER BY vi_maintaininfo.maintenanceDatetime desc";
                if (maininfo.Recordhead > -1 && maininfo.Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likename = "%" + maininfo.elevatorNo + "%";
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorID", maininfo.elevatorID);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(maininfo.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(maininfo.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", maininfo.Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail", maininfo.Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<MaintainInfo>(dataTable);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }

        }

        public int QueryMaintainPlanCount(MaintainInfo maininfo)
        {
            int count = 0;
            try
            {
                string strSql = "SELECT COUNT(1) FROM vi_maintaininfo WHERE maintenancestateId!=4";

                if (maininfo.elevatorID > 0)
                {
                    strSql += " AND vi_maintaininfo.elevatorID=@elevatorID";
                }
                if (!string.IsNullOrEmpty(maininfo.elevatorName))
                {
                    strSql += " AND vi_maintaininfo.elevatorName LIKE @elevatorName";
                }
                if (!string.IsNullOrEmpty(maininfo.startTime))
                {
                    strSql += " AND DATE(vi_maintaininfo.maintenanceDatetime) >=DATE(@startTime)";
                }
                if (!string.IsNullOrEmpty(maininfo.endTime))
                {
                    strSql += " AND DATE(vi_maintaininfo.maintenanceDatetime) <=DATE(@endTime)";
                }
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likename = "%" + maininfo.elevatorNo + "%";
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorID", maininfo.elevatorID);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(maininfo.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(maininfo.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch (Exception ex)
            {

            }

            return count;
        }

        public int GetElevatorID(string elevatorno)
        {
            string strSql = string.Format("SELECT id FROM elevator WHERE isDelete=0 AND elevatorNo='{0}'", elevatorno);
            int groupid = SqlHelper<MaintainInfo>.SqlScalar(strSql);
            return groupid;
        }

        public string DeleteMaintain(MaintainInfo maininfo)
        {
            string strSql = "delete FROM routine_maintain WHERE id=" + maininfo.id;

            try
            {
                if (SqlHelper<MaintainInfo>.SqlExecute(strSql))
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                LogHelper.Log(e.ToString());
                return "fail," + e.ToString();
            }
        }

        public List<WorkorderState> GetStateList()
        {
            string strSql = "SELECT * FROM workorderstate";

            return SqlHelper<WorkorderState>.SqlList(strSql);
        }

        public string UpdateMaintain(MaintainInfo maininfo)
        {
            string strSql = string.Empty;
            strSql = string.Format("UPDATE routine_maintain SET maintenancestateId={0},maintenanceProperties='{1}',receiver='{2}',receiverNo='{3}',receiverPhone='{4}',maintenanceDatetime={5},maintenanceResult='{6}',preparationTime={7},arriveTime={8},receiveTime={9},completeTime={10},acceptanceTime={11} WHERE id={12}"
                , maininfo.maintenancestateId, maininfo.maintenanceProperties, maininfo.receiver, maininfo.receiverNo, maininfo.receiverPhone, maininfo.maintenanceDatetime, maininfo.maintenanceResult, maininfo.preparationTime, maininfo.arriveTime, maininfo.receiveTime, maininfo.completeTime, maininfo.acceptanceTime, maininfo.id);
            try
            {
                if (SqlHelper<MaintainInfo>.SqlExecute(strSql))
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                LogHelper.Log(e.ToString());
                return "fail";
            }
        }

        public string CheckWorkNo(string workno)
        {
            string strsql = "select maintenanceNo FROM routine_maintain WHERE maintenanceNo='" + workno + "'";
            return SqlHelper<RepairWorkOrderInfo>.SqlScalarString(strsql);
        }

        public List<MaintainInfo> QueryAnnualMaintainHistoryList(MaintainInfo maininfo)
        {
            try
            {
                List<MaintainInfo> list = new List<MaintainInfo>();
                string strSql = "SELECT vi_maintaininfo.*,area.areaName AS mainAreaName FROM vi_maintaininfo LEFT JOIN area ON vi_maintaininfo.mainAreaId=area.id WHERE vi_maintaininfo.maintenancestateId=4 AND vi_maintaininfo.maintenanceProperties='年度保养'";

                if (!string.IsNullOrEmpty(maininfo.maintenanceNo))
                {
                    strSql += " AND vi_maintaininfo.maintenanceNo LIKE @maintenanceNo";
                }
                if (!string.IsNullOrEmpty(maininfo.elevatorName))
                {
                    strSql += " AND vi_maintaininfo.elevatorName LIKE @elevatorName";
                }
                if (maininfo.elevatorID > 0)
                {
                    strSql += " AND vi_maintaininfo.elevatorID=@elevatorID";
                }
                if (!string.IsNullOrEmpty(maininfo.elevatorNo))
                {
                    strSql += " AND vi_maintaininfo.elevatorNo LIKE @elevatorNo";
                }
                if (!string.IsNullOrEmpty(maininfo.startTime))
                {
                    strSql += " AND vi_maintaininfo.maintenanceDatetime >=' @startTime'";
                }
                if (!string.IsNullOrEmpty(maininfo.endTime))
                {
                    strSql += " AND vi_maintaininfo.maintenanceDatetime <='@endTime'";
                }

                strSql += " ORDER BY vi_maintaininfo.maintenanceDatetime Desc";
                if (maininfo.Recordhead > -1 && maininfo.Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likename = "%" + maininfo.maintenanceNo + "%";
                    string like_2 = "%" + maininfo.elevatorName + "%";
                    string like_3 = "%" + maininfo.elevatorNo + "%";
                    mySqlCommand.Parameters.AddWithValue("@maintenanceNo", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{like_2}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorNo", $"%{like_3}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorID", maininfo.elevatorID);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(maininfo.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(maininfo.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", maininfo.Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail", maininfo.Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<MaintainInfo>(dataTable);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public int QueryAnnualMaintainHistoryCount(MaintainInfo maininfo)
        {
            int count = 0;
            try
            {
                string strSql = "SELECT COUNT(1) FROM vi_maintaininfo WHERE maintenancestateId=4 AND maintenanceProperties='年度保养'";

                if (!string.IsNullOrEmpty(maininfo.maintenanceNo))
                {
                    strSql += " AND vi_maintaininfo.maintenanceNo LIKE @maintenanceNo";
                }
                if (!string.IsNullOrEmpty(maininfo.elevatorName))
                {
                    strSql += " AND vi_maintaininfo.elevatorName LIKE @elevatorName";
                }
                if (maininfo.elevatorID > 0)
                {
                    strSql += " AND vi_maintaininfo.elevatorID=@elevatorID";
                }
                if (!string.IsNullOrEmpty(maininfo.elevatorNo))
                {
                    strSql += " AND vi_maintaininfo.elevatorNo LIKE @elevatorNo";
                }
                if (!string.IsNullOrEmpty(maininfo.startTime))
                {
                    strSql += " AND vi_maintaininfo.maintenanceDatetime >=' @startTime'";
                }
                if (!string.IsNullOrEmpty(maininfo.endTime))
                {
                    strSql += " AND vi_maintaininfo.maintenanceDatetime <='@endTime'";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likename = "%" + maininfo.maintenanceNo + "%";
                    string like_2 = "%" + maininfo.elevatorName + "%";
                    string like_3 = "%" + maininfo.elevatorNo + "%";
                    mySqlCommand.Parameters.AddWithValue("@maintenanceNo", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{like_2}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorNo", $"%{like_3}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorID", maininfo.elevatorID);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(maininfo.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(maininfo.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch
            {

            }

            return count;
        }

        public string BatchDeleteMaintain(string ids)
        {
            string strSql = "delete FROM routine_maintain WHERE id in(" + ids + ")";

            try
            {
                if (SqlHelper<MaintainInfo>.SqlExecute(strSql))
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                LogHelper.Log(e.ToString());
                return "fail," + e.ToString();
            }
        }

        public List<T> ToList<T>(DataTable dt) where T : class, new()
        {
            List<T> list = new List<T>();
            //确认参数有效,若无效则返回Null
            if (dt == null)
                return list;
            else if (dt.Rows.Count == 0)
                return list;
            Dictionary<string, string> dicField = new Dictionary<string, string>();
            Dictionary<string, string> dicProperty = new Dictionary<string, string>();
            Type type = typeof(T);

            //创建字段字典，方便查找字段名
            foreach (var item in type.GetFields())
            {
                dicField.Add(item.Name.ToLower(), item.Name);
            }
            //创建属性字典，方便查找属性名
            foreach (var item in type.GetProperties())
            {
                dicProperty.Add(item.Name.ToLower(), item.Name);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //创建泛型对象
                T _t = Activator.CreateInstance<T>();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string memberKey = dt.Columns[j].ColumnName.ToLower();

                    //字段赋值
                    if (dicField.ContainsKey(memberKey))
                    {
                        FieldInfo theField = type.GetField(dicField[memberKey]);
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theField.FieldType;
                            //dbValue = dbValue.ChangeType(memberType);
                            dbValue = Convert.ChangeType(dbValue, memberType);
                        }
                        theField.SetValue(_t, dbValue);
                    }
                    //属性赋值
                    if (dicProperty.ContainsKey(memberKey))
                    {
                        PropertyInfo theProperty = type.GetProperty(dicProperty[memberKey]);
                        if (!theProperty.CanWrite) continue;
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theProperty.PropertyType;
                            if (memberType.IsGenericType && memberType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                            {
                                NullableConverter newNullableConverter = new NullableConverter(memberType);
                                dbValue = newNullableConverter.ConvertFrom(dbValue);
                            }
                            else
                            {
                                dbValue = Convert.ChangeType(dbValue, memberType);
                            }
                        }
                        theProperty.SetValue(_t, dbValue, null);
                    }
                }
                list.Add(_t);
            }
            return list;
        }

        public bool DeleteDistinctMaintain()
        {
            try
            {
                string strsql = @"DELETE t1 
                                    FROM
	                                    routine_maintain t1
	                                    JOIN routine_maintain t2 ON t1.elevatorID = t2.elevatorID 
	                                    AND DATE( t1.maintenanceDatetime ) = DATE( t2.maintenanceDatetime ) 
	                                    AND t1.maintenanceProperties = t2.maintenanceProperties 
	                                    AND t1.maintenanceNo = '' 
	                                    AND t1.id > t2.id";
                return SqlHelper<Elevator>.SqlExecute(strsql);
            }
            catch
            {
                return false;
            }
        }
    }
}
